/*
 * Created on Jun 6, 2005
 *
 */
package com.erclab.internal.xpresso.dataAccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.io.*;
import javax.sql.*;
import org.apache.commons.dbcp.*;
import org.apache.commons.dbutils.*;
import org.apache.log4j.*;

import com.erclab.internal.xpresso.exceptions.*;
import com.erclab.internal.xpresso.handlers.GenericResultsHandler;
import com.erclab.internal.xpresso.handlers.ReportResultsHandler;
import com.erclab.internal.xpresso.reports.Report;
import com.erclab.internal.xpresso.reports.ReportResultSet;

/**
 * @author Enrique Rodriguez.
 *
 */
public class MysqlDatasource extends ApplicationDataSource {

	//	Create the log for this class
	private static final Logger log = Logger.getLogger(MysqlDatasource.class);

	protected Properties properties;

	private void loadConfiguration() throws DataSourceException{
		properties = new Properties();
		byte[] bytes = this.parameters.getBytes();

		ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
		try {
			properties.load(bais);
		} catch (Exception ex){
			log.error("An error ocurred reading data source properties: " + ex);
			throw new DataSourceException ("An error ocurred reading data source properties: " + ex);
		}
		
		// Create a query builder
		this.queryBuilder = new MySqlQueryBuilder();
		
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.ApplicationDataSource#createDataSource()
	 */
	public void createDataSource() throws DataSourceException {
		log.info("Creating MysqlDatasource: Loading configuration");
		loadConfiguration();
		// Creates the repository data source
		try {
			log.info("Creating MysqlDatasource: Data source factory");
			this.setTheDataSource(BasicDataSourceFactory.createDataSource(properties));
			log.info("MysqlDatasource created succesfully");
			
		} catch (Exception ex){
			log.info("An error ocurred creating MysqlDatasource:" + ex);
			throw new DataSourceException("Error creating data source: " + ex.toString());
		}

	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.ApplicationDataSource#runReportQuery(com.erclab.internal.xpresso.reports.Report, java.util.HashMap, java.lang.String, java.lang.String, int, com.erclab.internal.xpresso.security.User)
	 */
	public ReportResultSet runReportQuery(Report theReport)
		throws DataSourceException {
			
			ReportResultSet rrs = null;
			Query q = new Query();
			q.setQuery(theReport.getQuery());
			queryBuilder.applyFiltersToQuery(q, theReport.getReportFilters(), theReport.getReportParameters().getConditions());
			queryBuilder.addSortByToQuery(q, theReport.getSortBy(), theReport.getReportParameters().getSortColumn());
			queryBuilder.replaceUniqueKeyValue(q, theReport.getReportParameters().getUniqueKeyValue());
			queryBuilder.replaceRequestParameters(q, theReport.getReportParameters().getRequestParameters());
			queryBuilder.replaceUserInfo(q, theReport.getReportParameters().getUser());
			
			Query maxResultsQuery = null;
			if(theReport.getRecordsPerPage() > 0){
				maxResultsQuery = queryBuilder.createMaxResultsQuery(q);
				queryBuilder.addPageNumber(q, theReport.getReportParameters().getPageNum(), theReport.getRecordsPerPage());
			}
			
			// TODO: Don't know if parent key value is necessary
			// query = queryBuilder.replaceParentKeyValue(query, theReport.getParameters().getParentKeyValue());
		
			ReportResultsHandler rrsHandler = new ReportResultsHandler(theReport);

			QueryRunner run = new QueryRunner((DataSource)theDataSource);
			try {
				rrs = (ReportResultSet)run.query(q.getQuery(), q.getValues(), rrsHandler);
				String maxNumberOfResults = "0";
				if (maxResultsQuery != null){
					GenericResultSet grs = (GenericResultSet)run.query(maxResultsQuery.getQuery(), 
												maxResultsQuery.getValues(), 
												new GenericResultsHandler());
					if(grs.nextRow())
						maxNumberOfResults = grs.getRow()[0];
				}
				rrs.setMaxNumberOfResults(Integer.parseInt(maxNumberOfResults));
			}catch(SQLException ex){
				throw new DataSourceException("Error running report '" + theReport.getReportName() + 
												"' query: " + theReport.getQuery() + ex.toString());
			}	
			
		return rrs;
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.ApplicationDataSource#runQuery(java.lang.String)
	 */
	public GenericResultSet runQuery(String query) throws DataSourceException {
		GenericResultSet grs = null;
		QueryRunner run = new QueryRunner((DataSource)theDataSource);
		GenericResultsHandler resultsHandler = new GenericResultsHandler();
		
		try {
			grs = (GenericResultSet)run.query(query, null, resultsHandler);
		}catch(SQLException ex){
			throw new DataSourceException(ex.toString());
		}	
		
		return grs;
	}

	public void executeQuery(Query theQuery) throws DataSourceException{
		log.info("--- executeQuery called: ");
		log.info("Query to execute: " + theQuery.getQuery());
		for (int i = 0; i< theQuery.getValues().length; i++){
			log.info("Values: " + theQuery.getValues()[i]);
		}

		Connection conn = null;
		try {
			conn = ((BasicDataSource)theDataSource).getConnection();
			PreparedStatement pstmt = conn.prepareStatement(theQuery.getQuery());
			for (int i =0; i< theQuery.getValues().length; i++){
				pstmt.setString(i+1, theQuery.getValues()[i]);
			}
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new DataSourceException("" + e);
		} finally{
			try { DbUtils.close(conn); } catch (Exception e){}
		}
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.ApplicationDataSource#executeUpdate(java.util.ArrayList, java.lang.String, java.lang.String, java.lang.String)
	 */
	public void executeUpdate(ArrayList attributes, String destinationObject, String uniqueKeyName, String uniqueKeyValue) throws DataSourceException {
		Query theQuery = queryBuilder.createUpdateQuery(attributes, destinationObject, uniqueKeyName, uniqueKeyValue);
		executeQuery(theQuery);
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.ApplicationDataSource#executeInsert(java.util.ArrayList, java.lang.String, java.lang.String, java.lang.String)
	 */
	public void executeInsert(ArrayList attributes, String destinationObject, String uniqueKeyName,	String uniqueKeyValue)
		throws DataSourceException {
		Query theQuery = queryBuilder.createInsertQuery(attributes, destinationObject, uniqueKeyName, uniqueKeyValue);
		executeQuery(theQuery);
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.ApplicationDataSource#executeDelete(java.lang.String, java.lang.String, java.lang.String)
	 */
	public void executeDelete(String destinationObject, String uniqueKeyName, String uniqueKeyValue) throws DataSourceException {
		Query theQuery = queryBuilder.createDeleteQuery(destinationObject, uniqueKeyName, uniqueKeyValue);
		executeQuery(theQuery);
		
	}

	public GenericResultSet runQuery(Query q)throws DataSourceException {
		GenericResultSet grs = null;
		QueryRunner run = new QueryRunner((DataSource)theDataSource);
		GenericResultsHandler resultsHandler = new GenericResultsHandler();
				
		try {
			grs = (GenericResultSet)run.query(q.getQuery(), q.getValues(), resultsHandler);
		}catch(SQLException ex){
			throw new DataSourceException(ex.toString());
		}	
				
		return grs;
	}

	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.dataAccess.ApplicationDataSource#executeMultipleValueQuery(java.lang.String, java.util.ArrayList, java.lang.String, java.lang.String, java.lang.String)
	 */
	public void executeMultipleValueQuery(String action,
									ArrayList attributes,
									String table,
									String uniqueKeyName,
									String uniqueKeyValue) throws DataSourceException {
		
		// Wee need to delete the records before we insert the new values
		String deleteSQL = "DELETE FROM " + table + " WHERE " 
						+ uniqueKeyName + "=?";
		Query q = new Query();
		q.setQuery(deleteSQL);
		q.setValues(new String[]{uniqueKeyValue});
		this.executeQuery(q);

		// Create the inserts and updates
		for (int i = 0; i < attributes.size(); i++){
			Attribute a = (Attribute) attributes.get(i);
			String[] values = a.getValues();
			if (values != null){
				for (int j=0; j < values.length; j++){
					// Insert new elements
					q.setQuery("INSERT INTO " + table + " (" + a.getName() + ", " + uniqueKeyName + ")"
								+ " values(?, ?)");
					q.setValues(new String[]{a.getValues()[j], uniqueKeyValue});
					this.executeQuery(q);
				}
			}
		}
	}

}
